/*
 Navicat Premium Data Transfer

 Source Server         : YL
 Source Server Type    : MySQL
 Source Server Version : 80032
 Source Host           : localhost:3306
 Source Schema         : bookinfo

 Target Server Type    : MySQL
 Target Server Version : 80032
 File Encoding         : 65001

 Date: 11/12/2023 08:40:21
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `book_id` int NOT NULL AUTO_INCREMENT,
  `book_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `book_num` int NOT NULL,
  `book_sort` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `book_record` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`book_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES (1, '计算机应用基础', 0, '计算机', '2018-07-13 00:00:00');
INSERT INTO `book` VALUES (2, 'C语言程序设计教程', 1, '计算机', '2021-05-05 00:00:00');
INSERT INTO `book` VALUES (3, '建筑识图与构造', 1, '工程  ', '2019-03-04 00:00:00');
INSERT INTO `book` VALUES (4, '商业银行会计学', 1, '经管  ', '2018-12-01 00:00:00');
INSERT INTO `book` VALUES (5, '内科护理学实训与学习指导', 1, '医学  ', '2019-01-01 00:00:00');
INSERT INTO `book` VALUES (6, '测量技术基础', 1, '工程  ', '2017-05-30 00:00:00');
INSERT INTO `book` VALUES (7, '中医诊断学', 0, '医学  ', '2018-01-01 00:00:00');
INSERT INTO `book` VALUES (8, '电子商务网站运营与管理', 1, '经管 ', '2022-07-15 00:00:00');
INSERT INTO `book` VALUES (9, 'MySQL数据库技术', 0, '计算机', '2018-04-01 00:00:00');
INSERT INTO `book` VALUES (10, 'Linux操作系统', 1, '计算机', '2021-06-25 00:00:00');
INSERT INTO `book` VALUES (11, 'Python编程基础', 1, '计算机', '2017-05-01 00:00:00');
INSERT INTO `book` VALUES (12, '大数据分析', 1, '计算机', '2022-07-01 00:00:00');

-- ----------------------------
-- Table structure for borrow
-- ----------------------------
DROP TABLE IF EXISTS `borrow`;
CREATE TABLE `borrow`  (
  `borrow_id` int NOT NULL AUTO_INCREMENT,
  `student_id` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `book_id` int NOT NULL,
  `borrow_date` datetime NOT NULL,
  `expect_return_date` datetime NOT NULL,
  `status` int NOT NULL DEFAULT 1,
  PRIMARY KEY (`borrow_id`) USING BTREE,
  INDEX `stu_bor`(`student_id` ASC) USING BTREE,
  INDEX `book_bor`(`book_id` ASC) USING BTREE,
  CONSTRAINT `book_bor` FOREIGN KEY (`book_id`) REFERENCES `book` (`book_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `stu_bor` FOREIGN KEY (`student_id`) REFERENCES `student` (`stu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of borrow
-- ----------------------------

-- ----------------------------
-- Table structure for returns
-- ----------------------------
DROP TABLE IF EXISTS `returns`;
CREATE TABLE `returns`  (
  `return_id` int NOT NULL AUTO_INCREMENT,
  `borrow_id` int NOT NULL,
  `borrow_date` datetime NOT NULL,
  `return_date` datetime NOT NULL,
  PRIMARY KEY (`return_id`) USING BTREE,
  INDEX `bor_ret`(`borrow_id` ASC) USING BTREE,
  CONSTRAINT `bor_ret` FOREIGN KEY (`borrow_id`) REFERENCES `borrow` (`borrow_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of returns
-- ----------------------------

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `stu_id` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `stu_name` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `stu_class` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `stu_specialty` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `stu_college` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `stu_integrity` int NOT NULL DEFAULT 1,
  PRIMARY KEY (`stu_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('2020010001', '刘佳佳', '203大数据', '大数据技术与应用', '信息工程学院', 1);

-- ----------------------------
-- Table structure for ticket
-- ----------------------------
DROP TABLE IF EXISTS `ticket`;
CREATE TABLE `ticket`  (
  `borrow_id` int NOT NULL,
  `over_date` int NOT NULL,
  `ticket_fee` float NOT NULL,
  PRIMARY KEY (`borrow_id`) USING BTREE,
  CONSTRAINT `bor_tic` FOREIGN KEY (`borrow_id`) REFERENCES `borrow` (`borrow_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of ticket
-- ----------------------------

-- ----------------------------
-- Procedure structure for proc_borrow
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_borrow`;
delimiter ;;
CREATE PROCEDURE `proc_borrow`(in s_id int,in b_id int)
begin 
if (select stu_integrity from student where stu_id=s_id)=1 and (select book_num from book where book_id=b_id)=1
then 
insert into borrow(student_id,book_id,borrow_date,expect_return_date)
values(s_id,b_id,now(),adddate(now(),interval 30 day));
else select '结束失败';
end if;
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_gen_ticket
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_gen_ticket`;
delimiter ;;
CREATE PROCEDURE `proc_gen_ticket`(in cur_date datetime)
begin
replace into ticket(borrow_id,over_date,ticket_fee) 
select  borrow_id,datediff(cur_date,expect_return_date),0.1*datediff(cur_date,expect_return_date) from borrow
where status=1 and cur_date>expect_return_date;
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_return
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_return`;
delimiter ;;
CREATE PROCEDURE `proc_return`(in b_id int)
BEGIN
if EXISTS(select ticket_fee from ticket where borrow_id=b_id)

then select '请交罚金';
elseif NOT EXISTS(select * from borrow where borrow_id=b_id)
   
   then select '没有借阅记录';
elseif (select status from borrow where borrow_id=b_id)=0
   
   then select '已归还';
else
   
   insert into returns(borrow_id, borrow_date, return_date) 
select borrow_id, borrow_date,now() from borrow where
borrow_id=b_id;
   update borrow set status=0 where borrow_id=b_id;
end if;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for stu_register
-- ----------------------------
DROP PROCEDURE IF EXISTS `stu_register`;
delimiter ;;
CREATE PROCEDURE `stu_register`(in stu_id char(15),in stu_name char(10))
begin
insert into student
select sno,sname,classname,specialty,college,1 from stuinfo.学生信息
where exists(select * from stuinfo.学生信息 where sno=stu_id and sname=stu_name) and sno=stu_id;
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table borrow
-- ----------------------------
DROP TRIGGER IF EXISTS `trigger_borrow`;
delimiter ;;
CREATE TRIGGER `trigger_borrow` AFTER INSERT ON `borrow` FOR EACH ROW BEGIN
    update book set book_num = book_num-1 
    where book_id = new.book_id;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table returns
-- ----------------------------
DROP TRIGGER IF EXISTS `trigger_return`;
delimiter ;;
CREATE TRIGGER `trigger_return` AFTER INSERT ON `returns` FOR EACH ROW BEGIN
    update book set book_num = book_num+1 
    where book_id=(select book_id from borrow where borrow_id=new.borrow_id);
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;
